package com.hqyj.springBoot.modules.test.dao;

import com.hqyj.springBoot.modules.common.entity.SearchBean;
import com.hqyj.springBoot.modules.test.entity.City;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

//扫描
@Mapper
@Repository
public interface CityDao {

    @Insert("insert into test_city (city_id, city_name, local_city_name, country_id, " +
            "district, population, date_modified, date_created) values (#{cityId}, " +
            "#{cityName}, #{localCityName}, #{countryId}, #{district}, #{population}, " +
            "#{dateModified}, #{dateCreated})")
    //将数据库中的ID映射到尸体类中 （使用生成的主键      实体类中的ID            数据库中的ID）
    @Options(useGeneratedKeys = true, keyProperty = "cityId", keyColumn = "city_id")
    void insertCity(City city);

    @Update("update test_city set city_name = #{cityName}, local_city_name = #{localCityName}," +
            "country_id = #{countryId}, district = #{district}, population = #{population}, " +
            "date_modified = #{dateModified} where city_id = #{cityId}")
    void updateCity(City city);

    @Delete("delete from test_city where city_id = #{cityId}")
    void deleteCity(int cityId);

    @Select("select * from test_city city left join test_country country on city.country_id = country.country_id where city_id = #{cityId}")
    City getCityByCityId(int cityId);

    @Select("select * from test_city where country_id = #{countryId}")
    List<City> getCityByCountryId(int countryId);

    @Select("<script>"
            + "select ci.*, co.country_name from test_city ci left join test_country co "
            + "on ci.country_id = co.country_id "
            + "<where> "
            + "<if test='keyWord != \"\" and keyWord != null'>"
            + " and (ci.city_name like '%${keyWord}%' "
            + "or ci.local_city_name like '%${keyWord}%' "
            + "or co.country_name like '%${keyWord}%') "
            + "</if>"
            + "</where>"
            + "<choose>"
            + "<when test='orderBy != \"\" and orderBy != null'>"
            + " order by ${orderBy} ${direction}"
            + "</when>"
            + "<otherwise>"
            + " order by city_id desc"
            + "</otherwise>"
            + "</choose>"
            + "</script>")
    List<City> getCityBySearchBean(SearchBean searchBean);
}
